﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using System.IO;
using System.Data.OleDb;

namespace Technoera.Common.Interop.OleDb
{
    public class XlsExportTask
    {
        public XlsExportTask(string xlsFullFileName)
        {
            XlsFullFileName = xlsFullFileName;
        }

        public string XlsFullFileName { get; private set; }

        private IList<IExportMapping> _mappings;
        public virtual IList<IExportMapping> Mappings
        {
            get
            {
                if (_mappings == null)
                    _mappings = new Collection<IExportMapping>();

                return _mappings;
            }
        }

        public void CreateTargetSheet(string sheetName)
        {
            using (OleDbConnection cn = new OleDbConnection(XlsHelper.GetExcelConnectionString(XlsFullFileName)))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = cn;
                    StringBuilder log = new StringBuilder();
                    try
                    {
                        cn.Open();
                        // Create sheet
                        SetupCommandForTableCreation(cmd, sheetName);
                        log.AppendLine("Table creation CommandText:");
                        log.AppendLine(cmd.CommandText);
                        log.AppendLine();
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (cn.State != System.Data.ConnectionState.Closed)
                        {
                            try
                            {
                                cn.Close();
                            }
                            catch { }
                        }
                        ExportLog = string.Concat(ExportLog, log.ToString());
                    }
                }
            }
        }

        private string _exportLog;
        public string ExportLog
        {
            get
            {
                if (_exportLog == null)
                    _exportLog = string.Empty;

                return _exportLog;
            }
            set
            {
                _exportLog = value;
            }
        }
	

        public void Export<T>(IList<T> items, string sheetName)
        {
            // Open file and insert rows
            using (OleDbConnection cn = new OleDbConnection(XlsHelper.GetExcelConnectionString(XlsFullFileName)))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = cn;
                    StringBuilder log = new StringBuilder();
                    try
                    {
                        cn.Open();
                        //// Insert items
                        cmd.CommandText = GetInsertStatement(sheetName);
                        log.AppendLine("Insert items CommandText:");
                        log.AppendLine(cmd.CommandText);
                        log.AppendLine();
                        Int32 itemCounter = 0;
                        foreach (T item in items)
                        {
                            itemCounter++;
                            cmd.Parameters.Clear();
                            SetItemParameters(cmd, item);
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                log.AppendFormat("There was an error while exporting row {0}:{1}", itemCounter, Environment.NewLine);
                                log.AppendLine(ex.ToString());
                                log.AppendLine("Parameters:");
                                foreach (OleDbParameter param in cmd.Parameters)
                                {
                                    log.AppendFormat("> [{0}] (1): {2}", param.ParameterName, Enum.GetName(typeof(OleDbType), param.OleDbType), param.Value);
                                    log.AppendLine();
                                }
                                throw ex;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new XlsExportException(this, ex);
                    }
                    finally
                    {
                        if (cn.State != System.Data.ConnectionState.Closed)
                        {
                            try
                            {
                                cn.Close();
                            }
                            catch { }
                        }
                        ExportLog = string.Concat(ExportLog, log.ToString());
                    }
                }
            }

        }

        private void SetItemParameters(OleDbCommand cmd, object item)
        {
            foreach (IExportMapping mapping in Mappings)
            {
                OleDbParameter param = new OleDbParameter();
                param.ParameterName = GetParameterName(mapping);
                param.DbType = mapping.TargetColumnDataType.DataType;
                switch (param.OleDbType)
                {
                    case OleDbType.Date:
                    case OleDbType.DBDate:
                    case OleDbType.DBTimeStamp:
                        DateTime value = (DateTime)mapping.GetPropertyValue(item);
                        if (value == DateTime.MinValue || value == DateTime.MaxValue)
                        {
                            param.Value = Convert.DBNull;
                        }
                        else
                        {
                            param.Value = value;
                        }
                        break;
                    default:
                        param.Value = mapping.GetPropertyValue(item);
                        break;
                }
                cmd.Parameters.Add(param);
            }
        }

        private string GetInsertStatement(string sheetName)
        {
            List<string> fieldNames = new List<string>();
            List<string> fieldParameterNames = new List<string>();
            foreach (IExportMapping mapping in Mappings)
            {
                fieldNames.Add(string.Format("[{0}]", mapping.TargetColumnName));
                fieldParameterNames.Add(GetParameterName(mapping));
            }
            return string.Format("INSERT INTO [{0}] ( {1} ) VALUES ( {2} )", sheetName, string.Join(",", fieldNames.ToArray()), string.Join(",", fieldParameterNames.ToArray()));
        }

        private static string GetParameterName(IExportMapping mapping)
        {
            return string.Format("@{0}", mapping.TargetColumnName.Replace(" ", "___"));
        }

        private void SetupCommandForTableCreation(OleDbCommand cmd, string sheetName)
        {
            List<string> fieldDeclarations = new List<string>();
            foreach (IExportMapping mapping in Mappings)
            {
                fieldDeclarations.Add(GetFieldDeclarationText(mapping));
            }

            cmd.CommandText = string.Format("CREATE TABLE [{0}] ( {1} )", sheetName, String.Join(", ", fieldDeclarations.ToArray()));
        }

        private string GetFieldDeclarationText(IExportMapping mapping)
        {
            DataTypeDefinition dataTypeDefinition = mapping.TargetColumnDataType;
            string dataSizeDeclaration = string.Empty;
            if (mapping.TargetColumnDataType is SizedDataTypeDefinition)
            {
                dataSizeDeclaration = string.Format("({0})", ((SizedDataTypeDefinition)dataTypeDefinition).Size);
            }
            else if (mapping.TargetColumnDataType is DecimalDataTypeDefinition)
            {
                DecimalDataTypeDefinition decimalDataType = (DecimalDataTypeDefinition)dataTypeDefinition;
                dataSizeDeclaration = string.Format("({0}, {1})", decimalDataType.Precision, decimalDataType.Scale);
            }
            //return string.Format("[{0}] {1}{2} NULL", mapping.TargetColumnName, GetSqlDataTypeName(mapping.TargetColumnDataType.DataType), dataSizeDeclaration);
            return string.Format("[{0}] {1}{2} NULL", mapping.TargetColumnName, mapping.GetSqlDataTypeName(), dataSizeDeclaration);
        }
    }
}
